create  table jms_dm.dm_reback_timely_rate
(
date_time date COMMENT 'date_time' ,
waybill_no varchar(100) COMMENT '运单号' ,
actual_network_code varchar(100) COMMENT '实际/面单操作网点' ,
plan_scan_site_code varchar(100) COMMENT '应操作网点' ,
apply_network_code varchar(100) COMMENT '登记网点code' ,

examine_time datetime COMMENT '审核时间' ,
is_timely int COMMENT '是否及时，1及时，2不及时，3不考核' ,
order_source_code varchar(100) COMMENT '订单来源' ,
order_source_name varchar(100) COMMENT '订单来源' ,
register_type int COMMENT '登记类型,1是始发登记，2目的登记' ,
apply_type_code int COMMENT '退转类型,5转寄件,4退回件' ,
apply_network_name varchar(100) COMMENT '登记网点' ,
apply_time datetime COMMENT '登记时间' ,

plan_scan_site varchar(100) COMMENT '应操作网点' ,
plan_agent_code varchar(100) COMMENT '应操作网点代理区编码' ,
plan_agent_name varchar(100) COMMENT '应操作网点代理区名称',
plan_fran_code   varchar(100) COMMENT '应操作网点加盟商code',
plan_fran_name   varchar(100) COMMENT '应操作网点加盟商名称',
plan_manage_code varchar(100) COMMENT '应操作网点大区code',
plan_manage_name varchar(100) COMMENT '应操作网点大区名称',
actual_network_name varchar(100) COMMENT '实际/面单操作网点CODE' ,

actual_agent_code varchar(100) COMMENT '实际/面单操作网点代理区编码' ,
actual_agent_name varchar(100) COMMENT '实际/面单操作网点代理区名称' ,
actual_fran_code  varchar(100) COMMENT '实际/面单操作网点加盟商code',
actual_fran_name  varchar(100) COMMENT '实际/面单操作网点加盟商名称',
actual_manage_code varchar(100) COMMENT '实际/面单操作网点大区code',
actual_manage_name varchar(100) COMMENT '实际/面单操作网点大区code',
print_network_name varchar(100) COMMENT '面单打印网点' ,
print_network_code varchar(100) COMMENT '面单打印网点' ,
min_print_time datetime COMMENT '面单打印时间' ,
site_send_time datetime COMMENT '网点发件扫描时间' ,
actual_center_code varchar(100) COMMENT '实际交件中心code' ,
actual_center varchar(100) COMMENT '实际交件中心' ,
actual_end_time datetime COMMENT '实际到达时间' ,
plan_end_time datetime COMMENT '规划到达时间' ,
warehouse_time datetime COMMENT '入仓时间' ,
latest_warehousing_time varchar(100) COMMENT '最晚入仓时间' ,
is_equal_site varchar(100) COMMENT '1是，0否' ,
out_warehouse_time datetime COMMENT '出仓时间' ,
efficiency_time datetime COMMENT '时效签收时间' ,
type_flg varchar(100) COMMENT '1退转件，2拦截件' )
ENGINE=OLAP
DUPLICATE KEY(date_time,waybill_no,actual_network_code,plan_scan_site_code)
comment '退件及时率'
PARTITION BY RANGE (date_time) (
   START ('2022-10-01') END ('2022-12-09') EVERY (INTERVAL 1 day))
   DISTRIBUTED BY HASH(date_time,waybill_no,apply_network_code) BUCKETS 4
PROPERTIES (
'replication_num' = '1',
'dynamic_partition.enable' = 'true',
'dynamic_partition.time_unit' = 'DAY',
'dynamic_partition.time_zone' = 'Asia/Shanghai',
'dynamic_partition.start' = '-362',
'dynamic_partition.end' = '3',
'dynamic_partition.prefix' = 'p',
'dynamic_partition.buckets' = '4',
'in_memory' = 'false',
'storage_format' = 'V2'
)
